Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Les_Grosman
Regular Visitor

Get Values from one table not present in another filtered table

I have a table of facts and several tables of measures to supplement it.

I need to output a list of report names  that do not occur in the fact table for the selected date.

Les_Grosman_0-1686047541050.pngLes_Grosman_1-1686047575226.png

I will need to do similar manipulations with other measures (users, workgroups, etc.) And these measures can be both a source for the list of values and a filter for the fact table.

Any Ideas how to do it?

1 ACCEPTED SOLUTION
Les_Grosman
Regular Visitor

Found solution by myself. 

I have created mesure:

isMissingReport = 
VAR CurrentReportID = SELECTEDVALUE( report[id] )
VAR FilteredReportIDs =
    CALCULATETABLE (
        VALUES ( fact[reportid] )
    )
RETURN
IF (
    NOT CONTAINS ( FilteredReportIDs, fact[reportid], CurrentReportID ),
    "TRUE",
    "FALSE"
)

 and add this measure in visuals as filter.

As result have this picture:

Les_Grosman_1-1686136903194.png

All filters work. And I alse create same measures for other dims (user etc.)

View solution in original post

1 REPLY 1
Les_Grosman
Regular Visitor

Found solution by myself. 

I have created mesure:

isMissingReport = 
VAR CurrentReportID = SELECTEDVALUE( report[id] )
VAR FilteredReportIDs =
    CALCULATETABLE (
        VALUES ( fact[reportid] )
    )
RETURN
IF (
    NOT CONTAINS ( FilteredReportIDs, fact[reportid], CurrentReportID ),
    "TRUE",
    "FALSE"
)

 and add this measure in visuals as filter.

As result have this picture:

Les_Grosman_1-1686136903194.png

All filters work. And I alse create same measures for other dims (user etc.)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.