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.
Hello everyone,
I have an issue to correctly count the distinct products within dynamic filter options.
This is my simple data model which consists of only one table.
In the first column of my matrix I need to create a measure that calculates the number of products per country for the earliest available date and so far it's pretty easy.
I did it with following DAX expression and it is working:
My result:
But the problem occurs when I need to create the second measure which consists of the same conditions as the first one but additionaly there need to be following condition:
For Denmark, count only products which have value "DK Trade" in 'Sales Channel' column but in case of France, count only products which have value "FR Retail" in 'Sales Channel' column (It can be hard-coded which sales channel for which country).
I was trying to do something like below but it doesnt work.
M2 =
var _date = FIRSTDATE(Table[Creation Date])
var _value = CALCULATE(DISTINCTCOUNT(Table[Product Number]), Table[Creation Date]=_date,
Table[Sales Channel] = IF (Table[Country] = "Denmark", "DK Trade",
IF (Table[Country] = "France", "FR Retail", null))
)
RETURN IF(ISBLANK(_value),0,_value)
If you have any ideas, please let me know, I will appreciate your help.
Solved! Go to Solution.
@Anonymous , Assume first date filter is working
M2 =
var _date = FIRSTDATE(Table[Creation Date])
var _value = CALCULATE(DISTINCTCOUNT(Table[Product Number]),filter( Table[Creation Date]=_date && ((Table[Country] = "Denmark" && Table[Sales Channel] = "DK Trade") || (Table[Country] = "France" && Table[Sales Channel] = "FR Retail"))
))
RETURN IF(ISBLANK(_value),0,_value)
@Anonymous , Assume first date filter is working
M2 =
var _date = FIRSTDATE(Table[Creation Date])
var _value = CALCULATE(DISTINCTCOUNT(Table[Product Number]),filter( Table[Creation Date]=_date && ((Table[Country] = "Denmark" && Table[Sales Channel] = "DK Trade") || (Table[Country] = "France" && Table[Sales Channel] = "FR Retail"))
))
RETURN IF(ISBLANK(_value),0,_value)
Oh it seems to work like that! Thank you so much.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
When I did it as you said I got the error message:
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |