How to "NOT to include" unrelated data in the Power BI visualisation

DAX DAX Calculations
1

Hi Team,
I have added the payroll data together with my dashboard for P&L.
I would like to have drill it down the payroll data to employee level. But there is no relationship between Payroll and MTD P&L data, I have the visualization as attached.
How can I have it “not showing” ( blank data) for the following highlighted data.

image
image603×587 33.5 KB

Please see my masked data for PBIX and excel.
thank you.

Regards,
AyeMaskedData.pbix (2.2 MB) MaskedData.xlsx (3.1 MB)

2

Hello, in order to not show the highlighted data, you need to use ISINSCOPE as folllows:

Total MTD Actuals =
VAR _MTDActuals = 
CALCULATE( [Total Actuals], DATESMTD( DateTable[Date] ) ) 
RETURN
IF(
    ISINSCOPE( PayData[Category] ),
    BLANK(),
    _MTDActuals
)

For the second formula:

LYTD Actuals - 2 =
VAR SelectedYear = SELECTEDVALUE( DateTable[FY] ) 

VAR _Calculation = 
CALCULATE(
    SUM( Transactions[NetBalance] ),
    Transactions[Book] = "MAIN", 
    ALL( DateTable[Month] ),
    ALL( DateTable[Year] ),
    DateTable[FY] = SelectedYear - 1
) 

VAR _Result = 
IF(
    ISINSCOPE( PayData[Category] ),
    BLANK(),
    _Calculation
) 

RETURN
_Result

Final Visualization:

image
image699×849 166 KB

3 Likes
3

Hi Alexbadiu,
It was a great help. thank you.
I now have applied to my live data and I have conditional formatting in LIVE. It shows as follows:

image
image1144×362 28.6 KB

Can you please let me know how can I get rid of those flags.
I tried replicating in Power BI Masked data and I could not replicate the same issue. But the issues exist in LIVE data. Can you please let me know the areas to be check (to get rid of) those flags. once collapse the data, I still want to see those flags. However I do not want them when I expanded.
thank you.

Regards,
AyeMaskedData.pbix (1.2 MB)

4

@ammu,

The red flags are controlled by specific measures inside conditional formatting
For ex, below, the conditional formatting is based on the field: Total MTD Actual vs Budgets

image
image933×632 47 KB

You need to check that those specific measures are using ISINSCOPE. If they do, then the value would be blank, which is not entering the conditions needed to create an icon.

Try to take off the conditional formatting and create it from scratch using the new measures.

Best regards,
Alex

2 Likes
5

Hi @ammu, we’ve noticed that no response has been received from you since the 15th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

6

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

7

Thanks, EDNA and @alexbadiu.
They are all good now

Regards,
Aye