Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic filter values

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. 

Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    5 years ago

    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)

5 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago
    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's avatar
      Anonymous
      Not applicable
      5 years ago

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

       

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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