How to show a list of values present in the Dimension table but not in the Fact table.

[Vivdroid_C_4222]

I have a DistributorMaster table and a Distribuotors Billed table. Both the tables are connected via a common column called DistributorId

Distribuotors Billed table is a transactional table.

I have created a table visual that shows a list of Distributors from the master table.

I want to show only those distributors that are present in the master table but not in the transactional table for a the selected date range.

How can I do this ??

[Anonymous]

Hi @Vivdroid_C_4222,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @Vivdroid_C_4222,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @Vivdroid_C_4222,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[bhanu_gautam · Accepted solution]

@Vivdroid_C_4222 Create a calculated table that lists the distributors from the master table that are not in the transactional table for the selected date range.

DAX
DistributorsNotBilled =
VAR SelectedDateRange = VALUES('DateTable'[Date])
RETURN
FILTER(
DistributorMaster,
NOT (
DistributorMaster[DistributorId] IN
CALCULATETABLE(
VALUES(DistribuotorsBilled[DistributorId]),
DistribuotorsBilled[Date] IN SelectedDateRange
)
)
)

Create a measure to filter the visual based on the selected date range.

DAX
DistributorsNotBilledMeasure =
VAR SelectedDateRange = VALUES('DateTable'[Date])
RETURN
CALCULATE(
COUNTROWS(DistributorMaster),
FILTER(
DistributorMaster,
NOT (
DistributorMaster[DistributorId] IN
CALCULATETABLE(
VALUES(DistribuotorsBilled[DistributorId]),
DistribuotorsBilled[Date] IN SelectedDateRange
)
)
)
)