Subtracting by filtered data - Two datasets - New BI user

[PowerBInewb]

My company uses a series of resolve codes in our ticketing system. I have filtered these codes into a distinct count. This distinct count needs to be removed from our incomplete percentage that is displayed in a different visual (Marked as True=Rescheduled (this displays a percentage).

My question:

I wanted to subtract the instances of rescheduled calls based on the distinct filter of counted repair code (depicted by the 6 in the image) . What type of formula allows me to subtract based on filted conditions between two datasets?

Thanks.

[Phil_Seamark · Accepted solution]

ok, I think I understand what you are after but not 100% sure.

First of all, rather than use the values the way you have been, can you create Calculated Measures on each of your tables.

So on your _custom_eviews_SCReportrepariCodes_6 table create a measure like this:

Count of Calls 6 = CALCULATE(
        COUNTROWS('_custom_eviews SCReportRepairCodes_6') ,
        FILTER(
            '_custom_eviews SCReportRepairCodes_6' ,
            [RepairCode] = "EC")
            )

and then something similar on your Materials table.  Once you have these as measures you can create a 3rd measure which is the result of the first - the second.

[Phil_Seamark]

Can you please post a sample of your datasets

[PowerBInewb]

[v-sihou-msft · Accepted solution]

@PowerBInewb

In this scenario, I would rather tagging the Efficient Calls into False than substracting the count of Efficient Calls from the True category and adding that amount into False part.

If you use a calculated column to tagging a call is incomplete or not, you may add a nested IF statment like:

IF(RELATED('_custom_eviews SCReportRepairCodes_6'[Repair Code]="EC",False,True))

Regards,

[Phil_Seamark · Accepted solution]

ok, I think I understand what you are after but not 100% sure.

First of all, rather than use the values the way you have been, can you create Calculated Measures on each of your tables.

So on your _custom_eviews_SCReportrepariCodes_6 table create a measure like this:

Count of Calls 6 = CALCULATE(
        COUNTROWS('_custom_eviews SCReportRepairCodes_6') ,
        FILTER(
            '_custom_eviews SCReportRepairCodes_6' ,
            [RepairCode] = "EC")
            )

and then something similar on your Materials table.  Once you have these as measures you can create a 3rd measure which is the result of the first - the second.