how to calculate

[Anonymous]

Hi all,

I am trying to calculate like SQL query using dax function.

          SELECT COUNT(*) FROM TABLE

          WHERE ID IN (SELECT ID FROM TABLE WHERE TYPE = 'ISSUE')

          AND TYPE = 'USE';

Can anyone teach me how to use dax function and filter from below table?

  

Thank you in advance.

[amitchandak]

@Anonymous , Create a measure like

measure =
var _tab = summarize(filter(Table,Table[TYPE] = "ISSUE"), Table[ID])
return
countrows(filter(Table,Table[TYPE] = "USE" && Table[ID] in _tab))

[Anonymous]

Thank you for your answer.

I tried the way you told me.

But I don't know why "2022-01-02", "2022-01-03" doesn't come out of the results like below.

[amitchandak · Accepted solution]

@Anonymous , Try like

measure =
var _tab = summarize(filter(allselected(Table),Table[TYPE] = "ISSUE"), Table[ID])
return
countrows(filter(Table,Table[TYPE] = "USE" && Table[ID] in _tab))

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Thank you so much!!!

I solved it with the answer:)