Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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
 

3 REPLIES 3
TeigeGao
Solution Sage
Solution Sage

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
Not applicable

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
IdColumname1
1TPST
1TPST
1ECOM
2TPST
2TPST
3TPST
3ECOM
4NAV
4TSPT
4ECOM
4ECOM

 

Thanks in advance.

Anonymous
Not applicable

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors