Distinct count over multiple table issue

[zenisekd]

Hi Everyone, 
I have had an issue that I managed to solve, however, I cant quite understand, how did I do it... 
I have had 2 tables, each representing one warehouse A (RX výroba)  and B (ZL výroba). I have created a master table (Articles) with all articles we have in our database. Now I wanted to know, how many distinct articles we had in our warehouse. It took me a while, but I figured out that this could work:

Count of articles 2 = 
sumx(
    VALUES(
        'Articles'[Article Nr]),
        If(
            DISTINCTCOUNT('RX výroba'[Materiálové číslo]) > 0,
            1, if(
                DISTINCTCOUNT('ZL výroba'[Materiálové číslo]) > 0,
                1, 0
                 ) 
             )
        )

But it worked just partially. It did work in a table, concerning single values (giving them 1, when there was an article in A or B or either A and B and 0 when there wasn´t in either A or B, but it didnt work on the total count, so my card visual was worthless.
Then I created this:

Count of articles 1 = 
sumx(
    VALUES(
        'Articles'[Article Nr]),
        If('RX výroba'[Dist. A table]>0,1,
        if('RX výroba'[Dist B table]>0,1,0
        )
    )
  )

with 


Dist. A table = DISTINCTCOUNT('RX výroba'[Materiálové číslo])

Dist B table = DISTINCTCOUNT('ZL výroba'[Materiálové číslo])

 and voila, it did work ... now I am puzzled, I thought that both measures are the same, ... I thought that if I create a measure inside a measure, it is the same as if I refer to a measure that is created solo.... 

Now I have two questions:
a) why is it that I have created separate measures and refer to it, in order to get it to work ????

b) would you propose some better way to find out the distinct count over multiple tables? 

Cheers! 

David 

[mahoneypat]

Here is another approach to consider that may be simpler:

DistinctCount over Two Tables =
VAR __TableA =
    SELECTCOLUMNS ( TableA, "Values", TableA[Materials] )
VAR __TableB =
    SELECTCOLUMNS ( TableB, "Values", TableB[Materials] )
RETURN
    COUNTROWS ( DISTINCT ( UNION ( __TableA, __TableB ) ) )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[zenisekd]

works fine 🙂

I have also found out that the first formula works well, when I add CALCULATE in front of IFs 

Count of articles 2 = 
sumx(
    VALUES(
        'Articles'[Article Nr]),
        CALCULATE(
          If(
            DISTINCTCOUNT('RX výroba'[Materiálové číslo]) > 0,
            1, if(
                DISTINCTCOUNT('ZL výroba'[Materiálové číslo]) > 0,
                1, 0
                 ) )
             )
        )

 Not sure though why?

[lbendlin]

I would use a naturalinnerjoin against just the productid column.  You can't have that many products for this to be any slower than your daisy chaining.

If you want some syntax sugar you can use SWITCH() instead of nested IFs.

[zenisekd]

Thanks, but why is it, that one measure (which contains the distinct count inside of it) is not working and the other one (that refer to separate distinct count measures) works?