Perform Join on two exressions generated from single table in DAX
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
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.
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.