Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Having trouble to get the result out of these CASE statements. Sample data is attached below.
1)
sum(case when Column1='Yes' or Column2='Yes')then 1 else 0 end) TotalValBranches
2)
sum(case when (Column1 = 'Yes' or Column2 = 'Yes') and Current=0 and Expected=0 then 1 else 0 end) TotalAttBranches
3)
sum(case when Coulmn3 = 'Yes' then 1 else 0 end) TotalChBranches
Ho do I use these CASE statements as a DAX? I will be counting the Total row count and might use it as a card visual.
Also, is it better to create a calculated Measure from these CASE statements or a Custom Column and then use that as a DAX?
Thanks in Advance!
Solved! Go to Solution.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêshi, @BBIUser
Your formulas are M language in the Edit Queries.
For creating the calculate column by DAX
You could click Modeling ->New Column
Then use these formulae to create three column
TotalValBranches = IF([Column1] = "Yes" || [Column2] = " Yes", 1, 0)
TotalAttBranches = if (([Column1] = "Yes" || [Column2]= "Yes") && [Current]=0 && [Expected]=0, 1 , 0)
TotalChBranches = IF([Column3] = "Yes", 1 , 0)
Comparison
And then add a measure for TotalValBranches/TotalAttBranches by DIVIDE Function
TotalValBranches/TotalAttBranches = DIVIDE ( CALCULATE ( SUM ( 'Table'[TotalValBranches] ) ), CALCULATE ( SUM ( 'Table'[TotalAttBranches] ) ), 0 )
and drag it into card visual.
also, you could just create a measure like below:
Measure = DIVIDE ( CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', [Column1] = "Yes" || [Column2] = " Yes" ) ), CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', ( [Column1] = "Yes" || [Column2] = "Yes" ) && [Current] = 0 && [Expected] = 0 ) ), 0 )
Columns add size to your model and decrease performance since you are adding values to your model and they are static.
Measures are only calculated when they are called and also store on cache for next uses and they are dynamic.
here is my pbix, please try it.
Best Regards,
Lin
Hi @BBIUser,
In DAX you need to define the filter to make this calculations work correcly (in this case) in other you may need to use the Switch or calculate function.
Try the formulas below:
TotalValBranches = calculate (countrows(Table[Current] ; Table[Column1] ='Yes' || Table[Column2] ='Yes') TotalAttBranches = calculate (countrows(Table[Current] ; Table[Column1] ='Yes' || Table[Column2] ='Yes';Table[Current] = 0 ; Table[Expected] = 0 ) TotalChBranches = calculate (countrows(Table[Current] ; Table[Column3] ='Yes')
These 3 measures should give expected result.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsAppreciate your reply @MFelix.
Does the CALCULATE(COUNTROWS(.. DAX accept the Column name after the COUNTROWS? I was getting errors and the expression was expecting a Table Name.
I tried creating an 'Custom Columns' for all the CASE statements. Below is an example in order.
TotalValBranches
if ([Column1] = "Yes" or [Column2] = " Yes") then 1 else 0
TotalAttBranches
if (([Column1] = "Yes" or [Column2]= "Yes") and [Current]=0 and [Expected]=0) then 1 else 0
TotalChBranches
if (Column3 = "Yes") then 1 else 0
I am not confident creating a custom column is correct or not, but I am still having trouble defining DAX from these custom columns too.
My final result should show TotalValBranches/TotalAttBranches values that will be displayed in a card or multi-row card as (numerator and denominator). Finally the % from these Numtor and Denomtor.
hi, @BBIUser
Your formulas are M language in the Edit Queries.
For creating the calculate column by DAX
You could click Modeling ->New Column
Then use these formulae to create three column
TotalValBranches = IF([Column1] = "Yes" || [Column2] = " Yes", 1, 0)
TotalAttBranches = if (([Column1] = "Yes" || [Column2]= "Yes") && [Current]=0 && [Expected]=0, 1 , 0)
TotalChBranches = IF([Column3] = "Yes", 1 , 0)
Comparison
And then add a measure for TotalValBranches/TotalAttBranches by DIVIDE Function
TotalValBranches/TotalAttBranches = DIVIDE ( CALCULATE ( SUM ( 'Table'[TotalValBranches] ) ), CALCULATE ( SUM ( 'Table'[TotalAttBranches] ) ), 0 )
and drag it into card visual.
also, you could just create a measure like below:
Measure = DIVIDE ( CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', [Column1] = "Yes" || [Column2] = " Yes" ) ), CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', ( [Column1] = "Yes" || [Column2] = "Yes" ) && [Current] = 0 && [Expected] = 0 ) ), 0 )
Columns add size to your model and decrease performance since you are adding values to your model and they are static.
Measures are only calculated when they are called and also store on cache for next uses and they are dynamic.
here is my pbix, please try it.
Best Regards,
Lin
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |