COUNTROWS Function

[Anonymous]

Hello,

please I cannot understand why the DAX query below give an empty result. In fact, the function inside the COUNTROWS works well and the result is a table. But the COUNTROWS doesn't work.  Please I need your help...

NumberPresentNov21_and_AbsentDec21 = COUNTROWS(

INTERSECT(

(SELECTCOLUMNS(VAR Tab_TransdateLastMonth= CALCULATETABLE(VS, MONTH(VS[TransDate])=12 && YEAR(VS[TransDate])=2021 && (VS[PatientStatus]="Defaulter"))

RETURN

SUMMARIZE(Tab_TransdateLastMonth

,VS[BaselineCode], VS[PatientStatus] , "LastRDVDate", MAX(VS[TransDate])),"BaselineCode", VS[BaselineCode]))

,

(SELECTCOLUMNS(VAR Tab_TransdateLastMonth= CALCULATETABLE(VS, MONTH(VS[TransDate])=11 && YEAR(VS[TransDate])=2021 && (VS[PatientStatus]="On Tx"|| VS[PatientStatus]="Transfered In" || VS[PatientStatus]="Restarted Tx" ))

RETURN

SUMMARIZE(Tab_TransdateLastMonth

,VS[BaselineCode], "DateLastRDV",  MAX(VS[TransDate])),"BaselineCode", VS[BaselineCode])

))

)

[Anonymous]

Hello bcdobbs,

your are right. There was a filter context in my file. The query works well in an empty file. 

Thanks once more. 

Regards

[bcdobbs]

Hi,

If I'm understanding when you materialise the two tables as calculated tables separatly you end up with an "DateLastRDV" in both. The first one will have max date from Dec 21 and the second from Nov 21.

That is then a problem when you use INTERSECT because it returns a table which has all the rows in the first table that are also in the second table. Because no row can have the same date in both tables you get an empty table returned.

If that isn't it can you supply some dummy data in a pbix?

[Anonymous]

Hello bcdodds,

Thanks very much for your help.

In fact, the function INTERSECT has two parameters (tables). If you look deeply you will notice that each one of them(parameters) is a table with only one columns named "BaselineCode". The columns "DateLastRDV" are not kept in the both parameters of INTERSECT because I knew what you have just said, which is absolutely true (Look at SUMMARIZE).

Then the function INTERSECT works well when it is excuted without COUNTROWS. It returns a table with a single columns named "BaselineCode".

My challenge is to count the number of rows of the result of INTERSECT function; but the function COUNTROWS is not working.

I hope it is a little more clear now? If not, please come back to me.

[bcdobbs · Accepted solution]

Sorry, I'd focussed on the SUMMARIZE and totally missed what the SELECTCOLUMNS was doing!

When you add the COUNTROWS in have you tried dropping the measure into a card on an empty page with no report filters set. Just wondering if something is already in the filter context.