topic how to prepare measure that relates to a "temp table" in DAX Commands and Tips

how to prepare measure that relates to a "temp table"

R0bson — Thu, 16 Mar 2023 15:25:47 GMT

Hi,

I have problem to prepare calulation in PBI that gives me the same result like simple sql code below:

SELECT clid
into #qs"
FROM queue_stats_mv 
where date between StartDate and EndDate
group by clid
having count(distinct datetime)>1

select count(*)
from queue_stats_mv 
where date between StartDate and EndDate and clid not in (select clid from #qs)

I tried calculate something that gives me similary result like temp table #qs using calculatetable and summarize to create new table and define relations between new calculate table and queue_stats_mv but it doesn't work. I need prepare this calculation base on dynamic dates range from date filter in table queue_stats_mv. Any suggestions?

Re: how to prepare measure that relates to a "temp table"

lbendlin — Sat, 18 Mar 2023 00:26:32 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: how to prepare measure that relates to a "temp table"

R0bson — Sat, 18 Mar 2023 10:42:35 GMT

Hi, thanks for reply.

You can see 2 measures in PBI sample:
Measure with fixed date range - i put fixed date range in calculatetable and measure and this give me correct result 5082.
Measure with slicer date range- this is my measure that i need change something to use dynamic date range from slicer.
I expected the same result as in measure with fixed date range in period: 2023-03-01 - 2023-03-14 but using slicer.

https://drive.google.com/file/d/1arZ4re3DBcxeCGNPeZKNYxdpmDpzbNIQ/view

Re: how to prepare measure that relates to a "temp table"

lbendlin — Sat, 18 Mar 2023 12:52:55 GMT

You may be overcomplicating things a bit. Let the data model do the work for you.

Measure with slicer date range = var a = selectcolumns(CALCULATETABLE( Filter(SUMMARIZE( queue_stats_mv, queue_stats_mv[clid], "qty", COUNTROWS(queue_stats_mv) ), [qty] > 1 ), queue_stats_mv[event] in {"ABAN","AGENT_D"} ),"clid",[clid]) return CALCULATE( COUNTROWS(queue_stats_mv), NOT queue_stats_mv[clid] IN a,queue_stats_mv[event] in {"COMPLETE_C","COMPLETE_A"})

see attached. I removed all the unnecessary tables and added a necessary dates table.

Re: how to prepare measure that relates to a "temp table"

R0bson — Sat, 18 Mar 2023 14:40:13 GMT

Thank You very much. Your solution is very simple and efficiency !