Perform Join on two exressions generated from sin...

Anonymous · ‎05-14-2019

Hi
I'm trying to get distinct ID count from my table(TableName1) to show it on a barchart.

Scenario is, in my table 'TableName1' one Id may contains different values in 'ColumnName1'(TPST,ECOM,KLJI,GKUP) with multiple entries
(can be differ by 'status' column value.)
However here i'm not bothering about 'status' of 'ColumnName1' value but looking for IDs involved in TPST and ECOM(one time only) .
For this I'm trying to create a measure to get distinct id counts(Ids involved in 'TPST' and One time 'ECOM').
Table name is: TableName1
Column Name is : ColumnName1
Below is the expected DAX i have written for this scenario.

Number of IDs with TPST and 1 ECOM =
var TPST =FILTER(TableName1,TableName1[ColumnName1]="TPST")
var ECOM= FILTER(TableName1, SUMMARIZE(TableName1,
      TableName1[ID],
            "Summarizecount",COUNTROWS(TableName1)
),
   [Summarizecount]=1),TableName1[ColumnName1]="ECOM"
var IdsWithAtleastOneTPSTandOneECOMresult=DISTINCT(COUNTROWS(NATURALINNERJOIN(TPST,ECOM)))
RETURN
IdsWithAtleastOneTPSTandOneECOMresult

Joining coulumn would be (id)
Tried in above formatted DAX but seems it is not the way to join two datasets generating dynamically .
Any help would be appreciated

TeigeGao · ‎05-14-2019

Hi @Anonymous ,

Could you please share the sample data and expected result to us for analysis? Generally, we can use DISTINCTCOUNT() function to get the the sum of distinct items. To filter data, we can use CALCULATE() and FILTER() function combined with DISTINCTCOUNT() function.

Best Regards,

Teige

Anonymous · ‎05-16-2019

Hi TeigeGao

Thanks for replying, here is some sample data .

Required, distict id Counts with involvement in TPST and 1 ECOM . Ultimately it would be 2 from below (ids 1 and 3). Later on I will have requirements like distict id Counts with involvement in TPST and 2 ECOM and distict id Counts with involvement in TPST and 3 ECOM ... which can be solved from this solution.