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 guys,
I'm struggling with power bi DAX function.
I have table consists of IDs, Plan Month, Plan Year, SavedDate.
Back in Excel, I created 2 fields month and Plan Start manually.
For instance, I count the IDs filtered with Plan Month = April, Plan Year= 2018, SavedDate = March RETURN April = 10.
Another example, I count the IDs filtered with Plan Month = May, Plan Year= 2018, SavedDate = April RETURN May= 100.
I would like to know in power bi what would be the correct function for me to create a new calculated column or measure with different filters? or should created multiple measures?
Solved! Go to Solution.
hi, @Stuznet
You can try to use this formula
AprilStartPlan = SWITCH ( TRUE (), MAX ( DateTable[month] ) = "April", CALCULATE ( COUNT ( Data[ID] ), FILTER ( Data, [Plan Month] = "Apr" && [Plan Year] = "2018" && [SavedDate] = "March" ) ), MAX ( DateTable[month] ) = "March", CALCULATE ( COUNT ( Data[ID] ), FILTER ( Data, [Plan Month] = "Mar" && [Plan Year] = "2018" && [SavedDate] = "March" ) ) )
Result:
here is pbix, please try it.
https://www.dropbox.com/s/gdwrikrf2gkih4h/Calculate%20Column%20With%20Filters.pbix?dl=0
By the way, your error that one ) is missing, there should be two ")" in this.
Best Regards,
Lin
Sooo, is there a pattern to that at all? If not, you could use a SWITCH TRUE statement like:
Measure = SWITCH( TRUE(), MAX([Month]) = "April", CALCULATE(COUNT([ID]),FILTER('Table',[Plan Month]="April" && [Plan Year] = 2018 && [SavedDate]="March"), MAX([Month]) = "May", CALCULATE(COUNT([ID]),FILTER('Table',[Plan Month]="May" && [Plan Year] = 2018 && [SavedDate]="April"), )
@Greg_Decklerthank you for helping me. I used your provided solution but I'm getting an error
A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
AprilStartPlan = SWITCH(TRUE(), MAX('DateTable[month])="April", CALCULATE(COUNT(Data[ID]), FILTER(Data,[Plan Month] = "April" && [Plan Year] = "2018" && [SavedDate] = "March"), MAX(DateTable[month]) = "May", CALCULATE(COUNT(Data[ID]), FILTER(Data,[Plan Month] = "May" && [Plan Year] = "2018" && [SavedDate] = "April"))))
hi, @Stuznet
You can try to use this formula
AprilStartPlan = SWITCH ( TRUE (), MAX ( DateTable[month] ) = "April", CALCULATE ( COUNT ( Data[ID] ), FILTER ( Data, [Plan Month] = "Apr" && [Plan Year] = "2018" && [SavedDate] = "March" ) ), MAX ( DateTable[month] ) = "March", CALCULATE ( COUNT ( Data[ID] ), FILTER ( Data, [Plan Month] = "Mar" && [Plan Year] = "2018" && [SavedDate] = "March" ) ) )
Result:
here is pbix, please try it.
https://www.dropbox.com/s/gdwrikrf2gkih4h/Calculate%20Column%20With%20Filters.pbix?dl=0
By the way, your error that one ) is missing, there should be two ")" in this.
Best Regards,
Lin
Covering 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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |