Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 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!
Solved! Go to Solution.
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.
@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.
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.
@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.
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 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.
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.
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.
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |