cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Measure to produce columns in Matrix

I am wondering if there is a way to produce a measure that will populate columns in a matrix table. See below my single date selection in a slicer.  It produces a single column. When I do multiple selections, it populates the way I need it.

What I'd like is for a single selection to produce a range of dates. I have tried TopN, but it did not work (date selected, return Top 6 dates from the max date).

1 ACCEPTED SOLUTION
Super User

Hi @Chris2038
Thank you for posting this important question. Your description of your problem is very clear, yet sometimes more details are required in order to serve you with accurate solutions.
In order to simplify the issue for you I created the sample file based on one table (sales table). I will try to descrive the steps in the following screeshots:

Now you can create your filter measure:

``````Filter =
VAR DefaultInterval = 3
VAR SelectedInterval =
IF (
ISBLANK ( SELECTEDVALUE ( Intervals[Interval (Months)] ) ),
DefaultInterval,
SELECTEDVALUE ( Intervals[Interval (Months)] )
)
VAR SelectedNumIntervals =
SELECTEDVALUE ( 'Number of Intervals'[# of Intervals] )
VAR DatesInFilter =
ALL ( 'Date' )
VAR DateSelection =
SELECTEDVALUE ('Date Selection'[End of Month] )
VAR T1 =
FILTER (
DatesInFilter,
'Date'[Date] = 'Date'[End of Month]
&& 'Date'[End of Month] <= DateSelection
)
VAR T2 =
T1,
"@Ranking", RANKX ( T1, 'Date'[End of Month] )
)
VAR T3 =
T2,
"@Test", MOD ( [@Ranking], SelectedInterval )
)
VAR T4 =
FILTER (
T3,
[@Test] = 0 || [@Ranking] = 1
)
VAR T5 =
T4,
"@Ranking2", RANKX ( T4, [@Ranking],, DESC )
)
VAR T6 =
FILTER (
T5, [@Ranking2] <= SelectedNumIntervals
)
VAR T7 =
SELECTCOLUMNS (
T6,
"Date", 'Date'[Date],
"End of Month", 'Date'[End of Month]
)
VAR T8 =
INTERSECT ( 'Date', T7 )
VAR Result =
CALCULATE (
COUNTROWS ( T8 ),
REMOVEFILTERS ( 'Date' )
)
RETURN
Result   ``````

Please don't be terrified 🙂 I can help you go through the steps one by one. Please let me know if you need any help.

6 REPLIES 6
Super User

Hi @Chris2038
Thank you for posting this important question. Your description of your problem is very clear, yet sometimes more details are required in order to serve you with accurate solutions.
In order to simplify the issue for you I created the sample file based on one table (sales table). I will try to descrive the steps in the following screeshots:

Now you can create your filter measure:

``````Filter =
VAR DefaultInterval = 3
VAR SelectedInterval =
IF (
ISBLANK ( SELECTEDVALUE ( Intervals[Interval (Months)] ) ),
DefaultInterval,
SELECTEDVALUE ( Intervals[Interval (Months)] )
)
VAR SelectedNumIntervals =
SELECTEDVALUE ( 'Number of Intervals'[# of Intervals] )
VAR DatesInFilter =
ALL ( 'Date' )
VAR DateSelection =
SELECTEDVALUE ('Date Selection'[End of Month] )
VAR T1 =
FILTER (
DatesInFilter,
'Date'[Date] = 'Date'[End of Month]
&& 'Date'[End of Month] <= DateSelection
)
VAR T2 =
T1,
"@Ranking", RANKX ( T1, 'Date'[End of Month] )
)
VAR T3 =
T2,
"@Test", MOD ( [@Ranking], SelectedInterval )
)
VAR T4 =
FILTER (
T3,
[@Test] = 0 || [@Ranking] = 1
)
VAR T5 =
T4,
"@Ranking2", RANKX ( T4, [@Ranking],, DESC )
)
VAR T6 =
FILTER (
T5, [@Ranking2] <= SelectedNumIntervals
)
VAR T7 =
SELECTCOLUMNS (
T6,
"Date", 'Date'[Date],
"End of Month", 'Date'[End of Month]
)
VAR T8 =
INTERSECT ( 'Date', T7 )
VAR Result =
CALCULATE (
COUNTROWS ( T8 ),
REMOVEFILTERS ( 'Date' )
)
RETURN
Result   ``````

Please don't be terrified 🙂 I can help you go through the steps one by one. Please let me know if you need any help.

Frequent Visitor

@tamerj1 I cannot thank you enough for this thoroughly detailed repsonse. This is exactly the solution I was hoping for. Very clever with defining the variables as the 7 intervals.
I'm quite new here and from your response I now undertstand the importance of what @littlemojopuppy had mentioned with uploading a PBIX file and sample data in order to best allow others to help.

Super User

Thanks to you for sharing such issues and requirements. I'm also new to power bi and keen to learn by trying to answer questions. This is the first coding language I've EVER learned l'm not even into data analysis I'm just a mechanical Engineer . Just started few months ago and already fallen in love with DAX

Community Champion

@Chris2038 please refer to this post about how to get your question answered quickly.  Please post some sample data (or better a pbix with your model!) and what you would expect the outcome to be.

Frequent Visitor

Thanks for the tips! I will follow that format going forward.

No different in the example link you shared, I too am having trouble "thinking in DAX" on how to populate dynamic column headers based on single input values. In excel, the model would be as follows;

The date in the last of the 5 columns is the selected date in Cell B2.

The table should be 5 headers based on date selected.

Currently in the PBIX file I have one calendar table which all the fact tables are related to. There is obviously an "end of month field" from Power Query.

I am expecting the table to look like this

Community Champion

VERY different than described in the article...all you're providing is "this is the expected outcome".  No raw data, nothing about how the data is organized...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.