Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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. 

slacey7070_0-1642533639280.png

 

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. 

slacey7070_1-1642533901667.png

 

Calculated Column (Option 1)

Here is one option that works when making a calculated column

slacey7070_2-1642533974420.png

 

Calculated Column (Option 2)

Here is a second option that works for a calculated column

slacey7070_3-1642534023946.png

 

 

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

 

slacey7070_5-1642534831549.png

 

I saw one option on the internet about using [Value], thinking this might work, but doesn't seem to 

slacey7070_4-1642534626404.png

 

 

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I'll figure out the remaining on it.  I appreicate the push in the right direction, it's defenitely closer and almost there. Thanks again. 

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@Anonymous Ok, then?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

I'll figure out the remaining on it.  I appreicate the push in the right direction, it's defenitely closer and almost there. Thanks again. 

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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? 

slacey7070_0-1642606458289.png

 

parry2k
Super User
Super User

@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 YouTube.gif 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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. 

 

slacey7070_0-1642604208058.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.