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 all,
I have a date filter and would like to display one table with days granularity and the other one with a month. For example, If I select jan 2, using hierarchy filter I want the first table to display data based on days and the other table to produce a result based on a monthly basis. Is this possible?
So when I design it appears like this,
But I want something like this, I.e. the second table to produce aggregated month value even when jan 2 is selected.
When using visual interaction option, it displays the whole month but I wanted to see something specific to that month alone. Is this possible by anyways?
Thanks in advance.
Cheers,
Indhu.
Solved! Go to Solution.
Hi @indhu,
Follow below steps:
1. Create a calculated column Month in your table which has count.
Month = MONTH(Table1[Date])
2. Create a measure which will hold the count for selected month.
MonthlyCount = VAR SelectedMonth = VALUES(Table1[Month]) RETURN CALCULATE(SUM(Table1[Count]), FILTER(ALLEXCEPT(Table1,Table1[Date]) , Table1[Month]= SelectedMonth ) )
3. Create a Date table which will be used in slicers.
Date = ADDCOLUMNS ( CALENDAR ( MIN ( Table1[Date] ), MAX ( Table1[Date] ) ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ) )
4. Create a relationship on Date column between these two tables.
5. User Year > Month > Date heirarchy from this table in your hierarchy slicer and use MonthCount measure as count in your monthly summary table.
You have now got the desired result.
Refer to this pbix where I replicated your scenario and derived the result in case you get stuck.
Prateek Raina
Hi @indhu,
Follow below steps:
1. Create a calculated column Month in your table which has count.
Month = MONTH(Table1[Date])
2. Create a measure which will hold the count for selected month.
MonthlyCount = VAR SelectedMonth = VALUES(Table1[Month]) RETURN CALCULATE(SUM(Table1[Count]), FILTER(ALLEXCEPT(Table1,Table1[Date]) , Table1[Month]= SelectedMonth ) )
3. Create a Date table which will be used in slicers.
Date = ADDCOLUMNS ( CALENDAR ( MIN ( Table1[Date] ), MAX ( Table1[Date] ) ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ) )
4. Create a relationship on Date column between these two tables.
5. User Year > Month > Date heirarchy from this table in your hierarchy slicer and use MonthCount measure as count in your monthly summary table.
You have now got the desired result.
Refer to this pbix where I replicated your scenario and derived the result in case you get stuck.
Prateek Raina
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |