Dynamic filter values

[Anonymous]

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:

M1 =
var _date = FIRSTDATE(Table[Creation Date])
var _value = CALCULATE(DISTINCTCOUNT(Table[Product Number]), Table[Creation Date]=_date)
RETURN IF(ISBLANK(_value),0,_value)

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. 

[Fowmy]

@Anonymous 

Try this way:

M2 = 
var _country = SELECTEDVALUE(Table[Country])
var _date = FIRSTDATE(Table[Creation Date])

var _value = CALCULATE(DISTINCTCOUNT(Table[Product Number]), Table[Creation Date]=_date,

IF ( _country = "Denmark", FILTER( Table, [Sales Channel]  = "DK Trade"),
IF ( _country = "France"), FILTER( Table, [Sales Channel]  = "FR Retail")))


RETURN IF(ISBLANK(_value),0,_value)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[amitchandak · Accepted 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]

Oh it seems to work like that! Thank you so much.

[AllisonKennedy]

Try using conditions and, or && ||

M2 =
var _date = FIRSTDATE(Table[Creation Date])
var _value = CALCULATE(DISTINCTCOUNT(Table[Product Number]), Table[Creation Date]=_date,
(Table[Sales Channel] = "DK Trade" && Table[Country] = "Denmark") ||
Table[Country] = "France" && Table[Sales Channel] = "FR Retail"))
)
RETURN IF(ISBLANK(_value),0,_value)

[Anonymous]

When I did it as you said I got the error message: