Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear All,
I am unable to map data from two separate files into the report (screenshot below) based on sllcer selection -dummy file link mentioned for ref :
File link mentioned - Dummy file link
Please advise how to calculate the same/ what action to be taken.
Thanks and regards,
Pratz
HI @Anonymous,
After a check on your sample file, I found your nested many measure formulas with different filters, it may cause the filter confilt.
In addition, it looks like you enable the all function in your formula with will ignore all filter effect, please use allselected function to instead.
Optimizing nested iterators in DAX
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT
Regards,
Xiaoxin Sheng
Thank you @Xiaoxin Sheng.
Apologies for sharing an incorrect measure, which i forgot to delete.
As suggested, have incorporated only the relevant ones.
Please advise on the mapping of target/ budget with Actuals.
attaching the updated file for ref : Dummy File
Regards,
Pratz
HI @Anonymous,
You can try to use the following measure formulas to replace your raw measures if they meet for your requirement:
N_YTD Exits =
VAR currDate =
MAX ( base[Date] )
RETURN
CALCULATE (
[Exits],
FILTER (
ALLSELECTED ( base ),
YEAR ( base[Date] ) = YEAR ( currDate )
&& MONTH ( base[Date] ) = MONTH ( currDate )
&& base[Date] <= currDate
)
)
N_YTD_AVG_HC =
VAR currDate =
MAX ( base[Date] )
RETURN
CALCULATE (
[MTD AVG HC],
FILTER (
ALLSELECTED ( base ),
YEAR ( base[Date] ) = YEAR ( currDate )
&& MONTH ( base[Date] ) = MONTH ( currDate )
&& base[Date] <= currDate
)
)
Time Intelligence "The Hard Way" (TITHW)
Regards,
Xiaoxin Sheng
Hi Xiaoxin Sheng,
Thank You for your time.
My MTD and YTD avg HC is already sorted, no issue with that.
I just want a solution/ or advice to get the data from 2 other tables (target %and Budgeted nos.) into the table on BI, basis the selection of 3 slicers - for a single selection and also all the 3 different slicers selected together .
However, i am unable to achieve the desired outcome.
Regards,
Pratz.
Hi @Anonymous,
So you mean your records are stored in different tables and not similar to the dummy sample you shared? If this is a case, maybe you can try to create a calculated table to union table and add a column to remark the source table.
Merged =
UNION (
ADDCOLUMNS ( Table1, "Source", "Table1" ),
ADDCOLUMNS ( Table2, "Source", "Table2" ),
ADDCOLUMNS ( Table2, "Source", "Table3" )
)
After these steps, you can modify the measure formula to calculate on merge table and add a condition with source field to prevent the calculation across different tables.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |