Download the Power BI File ⬇ - goodly.co.in/5-super-helpful-dax-tricks Also, check out my courses on DAX & Data Modeling - goodly.co.in/learn-dax-powerbi/
I like your ‘Remove Spill Over’ solution! I’ve had this issue before, and my solution was to filter the calendar table back in Power Query. So, the future dates just don’t get loaded into the model. But your solution allows the future dates to remain in the model, which is good, in case you want to include projections or forecasts.
Here are the tips from the video on how to use DAX functions: 1. **Reverse filters from the facts table to the dimension table using expanded tables:** * Wrap the measure in the CALCULATE function with the fact table as the filter. * The fact table will be expanded to include all columns from the dimension table. * This allows you to apply filters from the dimension table to the fact table. 2. **Fixing the nasty totals that Power BI often gives out as wrong using visual level calculations:** * Create a visual level calculation for the running total. * Define the calculation formula, axis, blanks handling, and reset conditions. * Wrap the calculation in the ADDCOLUMNS function to add it to a summarized table. * Use the SUMX function to calculate the total based on the summarized table. 3. **Using the Max and the Min functions smartly instead of writing long if statements:** * Use the MIN function to determine the smaller value between two options. * Use the MAX function to select the larger value between a calculation and a blank. * This can simplify your calculations and make them more readable. 4. **Removing the spillover effects of calculation using a check column:** * Create a check column with a DAX formula that evaluates the condition for the spillover effect. * Format the column to hide the values where the condition is not met. * This can help you avoid displaying incorrect data. 5. **Finding duplicate rows based on a certain criteria using the group by function:** * Use the GROUPBY function to group the data by the desired columns. * Create a new column within the grouped table to count the occurrences of each combination. * Sort the table by the count column to identify duplicates. * This can help you identify and clean up duplicate data in your dataset. I hope these tips help you use DAX functions more effectively!
Thanks for the video! In tip 1, how do I display “-“ in place of blanks where data is no present? Relating my problem to this data model, I have a matrix visual with rows as products and bands as columns but let’s say a product can be in multiple bands. So, if I have 5 bands and 10 products where a product P1 is only present for 2 bands, I want to show “yes” in 2 columns and “No”/- in 3 columns.
Why not just use the calculate modifier : userelationship, bidirectional argument ? To force the filter back to the product dimension? The expanded table concept is a nice trick but could be very confusing to new users.
Cool,Down the memory lane on previous video on 'Filter between Dimension Tables | CALCULATE function Trick!' for trick No 01. BTW Goodly became Yellowish.But prefer white..
For the Tip No 05 I suppose that includes Nested Iterations: Outer iteration sets the row context, while inner iteration (FILTER) scans and applies conditions.
I solved problem #1 with this: Unique Bands = CALCULATE ( DISTINCTCOUNT ( Products[Band] ), CROSSFILTER( Sales[Product ID], Products[Product Code], Both ) ) Which way would be better and why? #2 It seems without ADDCOLUMNS it also works: Max Region Sales = SUMX( SUMMARIZE ( 'Calendar', 'Calendar'[Year], 'Calendar'[Month] ), MAXX ( VALUES ( Sales[Region] ), [Total Sales] ) )
Concerning tip 1: I just saw a video from SQLBI (ruclips.net/video/XSWXw-orvI4/видео.html) in which Alberto says to NEVER filter whole tables, only columns, exactly because of the expanded tables and the performance issues that can arise from them. In a small model like in the video, it might work fast, but if you have millions of rows perhaps not.
Could you please give me solution, I have two tables charges and payment my requirement is payment comes month on month based on charge bill month. Suppose jan2024 charge billed $100 so $40 payment comes in jan2024 , $20 payment comes in feb2024, $15 comes in March 2024 and so on so. Bill ID is unique column in both table. How to calculate and show in matrix as Row payment month Charge . Jan24. Feb24 mar24 Jan2024. $40. $20. $15 Feb2024. $60. $25 Mar2024. $50 Please give me solution in pawar bi.
The fact that we should calculate right total every time is not awesome at all. It is awful. And what is the use of getting sum of maxs? It would be much better to get in total the biggest max if it is max calculation, or overall average if i calculate average.
Download the Power BI File ⬇ - goodly.co.in/5-super-helpful-dax-tricks
Also, check out my courses on DAX & Data Modeling - goodly.co.in/learn-dax-powerbi/
I like your ‘Remove Spill Over’ solution! I’ve had this issue before, and my solution was to filter the calendar table back in Power Query. So, the future dates just don’t get loaded into the model. But your solution allows the future dates to remain in the model, which is good, in case you want to include projections or forecasts.
Great video Chandeep!!
Thanks from South Africa!!!
Thank you Chandeep for this Video, It's really helped me to explore or discover something that I don't know before.
I always enjoy your explanations. Thank you for these
Tip 2. Shoutout Greg Deckler for the fight.
Great video!
Here are the tips from the video on how to use DAX functions:
1. **Reverse filters from the facts table to the dimension table using expanded tables:**
* Wrap the measure in the CALCULATE function with the fact table as the filter.
* The fact table will be expanded to include all columns from the dimension table.
* This allows you to apply filters from the dimension table to the fact table.
2. **Fixing the nasty totals that Power BI often gives out as wrong using visual level calculations:**
* Create a visual level calculation for the running total.
* Define the calculation formula, axis, blanks handling, and reset conditions.
* Wrap the calculation in the ADDCOLUMNS function to add it to a summarized table.
* Use the SUMX function to calculate the total based on the summarized table.
3. **Using the Max and the Min functions smartly instead of writing long if statements:**
* Use the MIN function to determine the smaller value between two options.
* Use the MAX function to select the larger value between a calculation and a blank.
* This can simplify your calculations and make them more readable.
4. **Removing the spillover effects of calculation using a check column:**
* Create a check column with a DAX formula that evaluates the condition for the spillover effect.
* Format the column to hide the values where the condition is not met.
* This can help you avoid displaying incorrect data.
5. **Finding duplicate rows based on a certain criteria using the group by function:**
* Use the GROUPBY function to group the data by the desired columns.
* Create a new column within the grouped table to count the occurrences of each combination.
* Sort the table by the count column to identify duplicates.
* This can help you identify and clean up duplicate data in your dataset.
I hope these tips help you use DAX functions more effectively!
Thanks for the video! In tip 1, how do I display “-“ in place of blanks where data is no present? Relating my problem to this data model, I have a matrix visual with rows as products and bands as columns but let’s say a product can be in multiple bands. So, if I have 5 bands and 10 products where a product P1 is only present for 2 bands, I want to show “yes” in 2 columns and “No”/- in 3 columns.
Thank you for this topic, really helpful and useful, excellent 🎉
always helpful content..
❤the best of every thing❤
The reverse filters trick is simply badass awesome 😅
Why not just use the calculate modifier : userelationship, bidirectional argument ? To force the filter back to the product dimension? The expanded table concept is a nice trick but could be very confusing to new users.
Amazing ❤
Cool,Down the memory lane on previous video on 'Filter between Dimension Tables | CALCULATE function Trick!' for trick No 01.
BTW Goodly became Yellowish.But prefer white..
For the Tip No 05 I suppose that includes Nested Iterations: Outer iteration sets the row context, while inner iteration (FILTER) scans and applies conditions.
hi goodly do you need thumbnail designer at cheap rates?
Does the reverse filter start to become slow with millions of rows in the fact table?
For tip 3, can I just use 0 instead of BLANK()?
I solved problem #1 with this:
Unique Bands =
CALCULATE (
DISTINCTCOUNT ( Products[Band] ),
CROSSFILTER( Sales[Product ID], Products[Product Code], Both )
)
Which way would be better and why?
#2 It seems without ADDCOLUMNS it also works:
Max Region Sales =
SUMX(
SUMMARIZE (
'Calendar',
'Calendar'[Year],
'Calendar'[Month]
),
MAXX (
VALUES ( Sales[Region] ),
[Total Sales]
)
)
Expanded table makes so much sense if you had SQL background
Concerning tip 1: I just saw a video from SQLBI (ruclips.net/video/XSWXw-orvI4/видео.html) in which Alberto says to NEVER filter whole tables, only columns, exactly because of the expanded tables and the performance issues that can arise from them. In a small model like in the video, it might work fast, but if you have millions of rows perhaps not.
🎉🎉🎉
I wish you would speak slower, but your class is good.
Hindi wala course batao paji
Plz create video in hindi
Too advanced for me!
Could you please give me solution,
I have two tables charges and payment my requirement is payment comes month on month based on charge bill month.
Suppose jan2024 charge billed $100 so $40 payment comes in jan2024 , $20 payment comes in feb2024, $15 comes in March 2024 and so on so. Bill ID is unique column in both table. How to calculate and show in matrix as
Row payment month
Charge . Jan24. Feb24 mar24
Jan2024. $40. $20. $15
Feb2024. $60. $25
Mar2024. $50
Please give me solution in pawar bi.
The fact that we should calculate right total every time is not awesome at all. It is awful. And what is the use of getting sum of maxs? It would be much better to get in total the biggest max if it is max calculation, or overall average if i calculate average.
Bane of Power BI : totals.
It's an example only.
Make in hindi
This was really difficult to understand
Why you doing youtube