Perform Join on two exressions generated from single table in DAX

[Anonymous]

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]

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]

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.

Tablename1
Id	Columname1
1	TPST
1	TPST
1	ECOM
2	TPST
2	TPST
3	TPST
3	ECOM
4	NAV
4	TSPT
4	ECOM
4	ECOM

Thanks in advance.

[Anonymous]

Hi Community,

Can i get help on this question -Join on  two exressions generated from single table in DAX . I was stuck on this totally.

Thanks In Advance