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

[R0bson]

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?

[R0bson]

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

[lbendlin · Accepted solution]

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.

[R0bson]

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

[lbendlin]

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-...
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