Forum Discussion
How to get counts of temp table variable ?
Hi All,
It would be great if someone has an idea how to figure this out.
I currently have six calendar tables that users can pull data from each of the ranges and get the sales in six different columns for those week ranges.
Goal is to create a flag that says if there is overlap between the ranges they've inputted as a warning.
Below is the example of the six calendar slicers.
- I've included the list of fiscal_year_weeks that each calendar would populate to see what the lists would look like.
I figured I would create a measure that unions all these lists together, then count the occurances of each, then create a flag if there are any instances of count > 1
I've been building this up by createing a calculated table, then a calculated column, and then planned to combine them both into a measure that I'd actually use.
Creating calulated table
Here I create variables for each calendars list of fiscal year week values.
Calculated Column (Option 1)
Here is one option that works when making a calculated column
Calculated Column (Option 2)
Here is a second option that works for a calculated column
Problem
When I try using these calculated columnns in the calulcuated table itself, it won't let me reference the fiscal year week column.
For example, here's me trying to add a colmn with a new variable all_vals2 called "occurances3" using the logic from the calculated column above titled "occurances" and it provides an error. Same goes for when I tried using the logic in the "occurances 2" calculated column
I saw one option on the internet about using [Value], thinking this might work, but doesn't seem to
Ideally I can create a calculated table with all the "Fiscal Year Week's" like I have above, after the slicer ranges are selected. Then have the counts of each 'Fiscal Year Week' in that temp table. Then be able to return a flag if there are any counts > 1 in that temp table.
Thank you for any help on this one!
6 Replies
Anonymous maybe create a measure, you don't need to create a temp table:
Measure = VAR __unionTable = UNION ( .... ) RETURN SUMX ( SUMMARIZE ( __unionTable, [Fical_Year_Week Column], "@Count", COUNTROWS ( __unionTable ) ), IF ( [@Count] > 1, 1 ) )If this measure is not blank, it means there are overlapped dates, otherwise it will give the count of dates that are overlapped.
✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
I think it's getting closer, thank you - but not quite there yet.
If I just look at the inner (Summarize) Part of the function, and put it in a table temporarily (later I'll put it in a measure), to see what's going on, the Counts seem much higher, as there should only by 6 distinct counts of each fiscal_year_week, instead of the 942 it's reporting.
Anonymous because you are just doing a simple count, you didn't put the check for duplicates, so it makes sense why you are getting such a big number.
You are basically counting all the rows and my original measure is different.
To clarify, when I use the whole code you had sent over, it says there's lots of duplicates.
So, I used just the highlighted part of the code you sent in a table to see what it was doing, and like you're mentioning, this inner part of the function is counting all the rows instead of counting the dupilcates.
So, by the time it passes this data to the outter SUMX function, it makes sense that there are lots of duplicates, as it appears to be counting all the rows in the table. Does that make sense?
Anonymous Ok, then?
