Measure to calculate the distinct count of type from fact table using dim table

[yoleksandr]

Hi all!

Can you help me to build a DAX measure, please?

So I have two tables.
The first one looks like this, it shows the participating organizations (columns from partner1 to partner5) by location and sales for each row.

The second table is like this, it shows the type of each Organization. 

I need to write a DAX measure, that will calculate the distinct count of type of organizations, so that then I can filter it by location.

NB: I need to keep the same structure of the first table, with no changes/pivots in PowerQuery.

The correct result that I calculated in Excel looks like this:

Here I put the sample PBIX file:

https://drive.google.com/file/d/13hsVGQSBPjnBtTvhJv4g-ZesF3DdPE2e/view?usp=sharing

I know it's easy if I create a separate calculated table, but I need a measure for this, not a new table.

Would be very grateful for any advice on this!

[tamerj1 · Accepted solution]

H @yoleksandr and @DimaMD 

I don't have access to my laptop right now but you may try

=
COUNTROWS (
INTERSECT (
VALUES ( table2[Organization] ),
DISTINCT (
UNION (
VALUES ( table1[partner1] ),
VALUES ( table1[partner2] ),
VALUES ( table1[partner3] ),
VALUES ( table1[partner4] ),
VALUES ( table1[partner5] )
)
)
)
)

[tamerj1 · Accepted solution]

H @yoleksandr and @DimaMD 

I don't have access to my laptop right now but you may try

=
COUNTROWS (
INTERSECT (
VALUES ( table2[Organization] ),
DISTINCT (
UNION (
VALUES ( table1[partner1] ),
VALUES ( table1[partner2] ),
VALUES ( table1[partner3] ),
VALUES ( table1[partner4] ),
VALUES ( table1[partner5] )
)
)
)
)

[yoleksandr]

thanks a lot! works great!

[DimaMD · Accepted solution]

@tamerj1 Hi, so this measure gave the correct result, @yoleksandr  use the measure provided @tamerj1 

[yoleksandr]

UPDATE:
So I managed to create the unpivot with DAX with the following code:

sales_unpivot =
NATURALINNERJOIN(
TREATAS(
DISTINCT(FILTER(
UNION(
SELECTCOLUMNS(sales, "partner", sales[partner1], "location", sales[location]),
SELECTCOLUMNS(sales, "partner", sales[partner2], "location", sales[location]),
SELECTCOLUMNS(sales, "partner", sales[partner3], "location", sales[location]),
SELECTCOLUMNS(sales, "partner", sales[partner4], "location", sales[location]),
SELECTCOLUMNS(sales, "partner", sales[partner5], "location", sales[location])),
[partner] <> "")), dim_types[partner], sales[location]),dim_types)

It gives a table of this kind

Now the question is whether it is possible to create a measure out of the above formula, this measure should calculate the disctinct count of organization by type. But I don't need to create a separate table, only a measure (the full dataset is 200K entries, so additional table would be redundant and slow the performance).

[DimaMD]

@yoleksandr  yes try it measure, only I see that the total counts incorrectly

OrganizationsCount = 
VAR VirtualTable =
            NATURALINNERJOIN(
TREATAS(
DISTINCT(FILTER(
UNION(
SELECTCOLUMNS(sales, "partner", sales[partner1], "location", sales[location]),
SELECTCOLUMNS(sales, "partner", sales[partner2], "location", sales[location]),
SELECTCOLUMNS(sales, "partner", sales[partner3], "location", sales[location]),
SELECTCOLUMNS(sales, "partner", sales[partner4], "location", sales[location]),
SELECTCOLUMNS(sales, "partner", sales[partner5], "location", sales[location])),
[partner] <> "")), dim_types[Organization], sales[location]),dim_types)
          
VAR FilteredTable =
    CALCULATE( COUNTROWS(VirtualTable), FILTER(VirtualTable, [Organization] = SELECTEDVALUE(dim_types[Organization])))
RETURN
    FilteredTable
        
    

[yoleksandr]

hi @DimaMD ,
thanks a lot for this, I see we're really close to the correct solution, but not there yet.

The issue is that I need to have DISTINCTCOUNT of [partner] column from the virtual table, but the below variant of the FilteredTable is not working:

VAR FilteredTable =

    CALCULATE( DISTINCTCOUNT(VirtualTable[partner]), FILTER(VirtualTable, [Organization] = SELECTEDVALUE(dim_types[Organization])))

Do you know how to make it calculate the correct total?

[DimaMD]

@tamerj1 Hello , maybe you have an idea how to calculate it?

[DimaMD]

@yoleksandr  hi, look at the attached file and answer if this is the desired result

[yoleksandr]

hi @DimaMD ,

Thanks a lot for this, but it's not exactly what I need.
Sorry if it wasn't clear initially, I added one line to the fist message now.

So I need to keep the structure of the table, it should not be changed. You unpivoted the columns with partners, but with this the data in "sales" column is now repeated many times, so it gives the wrong result. Hope what I mean is clear.
in any case, Slava Ukraini!

[eliasayyy]

i think you can add a concatinating column where you concat values and then , see if it contains in the column teh value

[eliasayyy]

hello first pivot your first table for all 5 partners this way , you will have 2 columns , partners and organization column , 

next create 2 dim tabes one with distinct values of organizatio nand one with distinct values of partners

now link the tbales via one to many relationship 

[yoleksandr]

Thanks, but I need to keep the same structure of the table, no unpivots (sales column will give a wrong result in this case).