Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Counting dimension's attribute distinct values

Hi guys, I need your help. I have to count the distinct suppliers from witch I made at least a purchase, given a certain filter context. Since the relationship between the dimension table of the su...
distinctcount
facts filtering dimensions
filter direction
  • AlbertoFerrari's avatar
    AlbertoFerrari
    6 years ago

    Hi guys,

     

    The expanded version of Acquisti actually contains the full Fornitori table.

    Therefore, if you use Acquisti as a filter argument in CALCULATE, you are filtering Fornitori too. CROSSFILTER is useless in this case. You could obtain the same performing a DISTINCTCOUNT, use CROSSFILTER avoiding the variable at all.

    With that said, I would use something easier to read, like:

     

    COUNTROWS ( SUMMARIZE ( Acquisti, Fornitori[Fornitore - Descrizione] ) )

     

    Did not check performance, but it shouldn't be too bad, and definitely easier to read.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago
Can you just switch the relationship to Both?
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    The relationship has to be mono-directional. The point is that I would excpect the second formula expression to be working only after setting the relationship type to 'both', while it's working properly even with the relationship set to 'single'...

    • Mariusz's avatar
      Mariusz
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi Anonymous 

       

      The reason for 

       

      VAR DatiFatturato = CALCULATETABLE('Acquisti';'Acquisti'[Flag fatturato]=1)

       

      not being able to propagate the filters to 'Fornitori intestatari' is that 'Acquisti' within CALCULATETABLE is not expanded any more so it does not include 'Fornitori intestatari' in it.

       

      You can try the below if the granularity of 'Fornitori intestatari'[Fornitore - Descrizione] is different to 'Fornitori intestatari'[K_CodCli]

       

      Fornitori con fatturato =
CALCULATE(
    DISTINCTCOUNT( 'Fornitori intestatari'[Fornitore - Descrizione] );
    CROSSFILTER( 'Acquisti'[K_CodCli]; 'Fornitori intestatari'[K_CodCli]; BOTH );
    'Acquisti'[Flag fatturato] = 1
)

       

       

      AlbertoFerrari Please correct if I'm wrong, or elaborate so we can understand.

       

      Best Regards,
      Mariusz

      If this post helps, then please consider Accepting it as the solution.

      Please feel free to connect with me.
      LinkedIn

       

      • AlbertoFerrari's avatar
        AlbertoFerrari
        Icon for Most Valuable Professional rankMost Valuable Professional
        6 years ago

        Hi guys,

         

        The expanded version of Acquisti actually contains the full Fornitori table.

        Therefore, if you use Acquisti as a filter argument in CALCULATE, you are filtering Fornitori too. CROSSFILTER is useless in this case. You could obtain the same performing a DISTINCTCOUNT, use CROSSFILTER avoiding the variable at all.

        With that said, I would use something easier to read, like:

         

        COUNTROWS ( SUMMARIZE ( Acquisti, Fornitori[Fornitore - Descrizione] ) )

         

        Did not check performance, but it shouldn't be too bad, and definitely easier to read.