Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm trying to create a measure that indicates with a 1 or 0 based on the following data:
Year Month 2019 Jan 2019 Feb 2019 Mar 2019 Apr 2019 May 2019 Jun 2019 Jul 2019 Aug 2019 Sep 2019 Oct 2019 Nov 2019 Dec 2020 Jan 2020 Feb 2020 Mar
If the Month exists in the MAX Year, I will return a 1, otherwise 0.
So the measure should work like this if is added to a visual table:
Year Month isMonthInMaxYear 2019 Jan 1 2019 Feb 1 2019 Mar 1 2019 Apr 0 2019 May 0 2019 Jun 0 2019 Jul 0 2019 Aug 0 2019 Sep 0 2019 Oct 0 2019 Nov 0 2019 Dec 0 2020 Jan 1 2020 Feb 1 2020 Mar 1
I can't solve it statically because at the future there will be more records on the table and the 1's will be changing over time, neither can't create more tables at the data model (nor calculated tables or Power Query tables).
What I'm trying to do is to have a table a one-column table as VAR on the measure, but I'm kind of stuck because VALUES function doesn't accept criteria, SUMMARIZECOLUMNS asks for an index column in which criteria can't be applied and CALCULATE TABLE will return the table with Year and Month columns.
I'd like to have a table with the months of the highest year in a VAR, so the table would look like this.
Month Jan Feb Mar
And then use it on a condition using the IN function, but I'm stuck on the previous step.
Is it possible to do that? Is this approach correct?
Thanks
Solved! Go to Solution.
@_fmigg try this DAX
Is month found = VAR __maxYear = CALCULATE( MAX ( 'Table'[Year] ), ALL() ) VAR __months = CALCULATETABLE( VALUES( 'Table'[Month] ), 'Table'[Year] = __maxYear) RETURN COUNTROWS( __months ) + 0
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.
@_fmigg here is the output
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.
Hi @_fmigg
If your measure works correctly, you can just get that one-column table with:
FILTER( VALUES( Table1[Month] ), [isMonthInMaxYear] = 1)
and then use it in a VAR or wherever you need it. If this doesn't work do show the code for the measure as it migt need adjusting
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
@_fmigg try this DAX
Is month found = VAR __maxYear = CALCULATE( MAX ( 'Table'[Year] ), ALL() ) VAR __months = CALCULATETABLE( VALUES( 'Table'[Month] ), 'Table'[Year] = __maxYear) RETURN COUNTROWS( __months ) + 0
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.
@_fmigg here is the output
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |