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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LP2803
Responsive Resident
Responsive Resident

Many to one relationship is not filtering the data

Hi Team,

 

I have the below relationship between the tables. When I try to filter MeetingNumber (a slicer)  from "Sheet1", it isn't filtering values in visuals where i have used columns from "Sheet1(2)". I have the set the Cross fitler Direction to Both. but still in the visuals its not filtering.   

 

Any Idea what could be wrong here?

 

LP2803_0-1608835373296.png

 

LP2803_1-1608835546602.png

 

 

7 REPLIES 7
StefanoGrimaldi
Resident Rockstar
Resident Rockstar

I could be wrong (would need a example data to try your formula with the reference you using) but you trying inside a dax to create a filtered table inside another for a column dax formula; try making first the filtered table separated: 

 

New Table  No.1 = 

var table1 = FILTER('Sheet1 (2)','Sheet1 (2)'[Attribute.2]="packetLoss")

var table 2 = filter(table1, 'Sheet1 (2)'[Value]>5)

return table 2

 

this will get you the filtered versión of the table in a new table, leaving only the losses rows on it giving those parameters, if you want the table to summarize based on participantname you can change the new table dax further: 

New Table  No.1 = 

var table1 = FILTER('Sheet1 (2)','Sheet1 (2)'[Attribute.2]="packetLoss")

var table 2 = filter(table1, 'Sheet1 (2)'[Value]>5)

var table3 = summarize( table2, "participantname" <-- column name)

return table 3

this way you would have a table with a single column of all the participantname that lost. 

 

hope this was helpfull





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




StefanoGrimaldi
Resident Rockstar
Resident Rockstar

if you want, post over the excel file with 2 sheet containing only the references column to recreate the problem and see if can find the problem over there. 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




@StefanoGrimaldi I dont think there is an issue with the relation. 

 

I just was testing and felt that I have 2 measures with the below DAX in them which I think are causing the issue of not filtering.  when I filter the visual without these measures in it, it works perfectly fine.

 

Any idea how i can make this work?

 

PacketLoss ? = IF(SUMX ( DISTINCT ('Sheet1 (2)'[ParticipantName]), [PacketLossCalc] ) >0, 1, 0 )
 
PacketLossCalc = if(COUNTROWS( FILTER(
FILTER('Sheet1 (2)','Sheet1 (2)'[Attribute.2]="packetLoss"),
'Sheet1 (2)'[Value]>5)) > 3,1,0)

Hi @LP2803 ,

 

Would you please show us sample pbix by onedrive for business? In your formula, there is no function to avoid filter from table1. 

 

You can try to use the following measure:

PacketLossCalc =
IF (
    COUNTROWS (
        FILTER (
            'Sheet1 (2)',
            'Sheet1 (2)'[Attribute.2] = "packetLoss"
                && 'Sheet1 (2)'[Value] > 5
        )
    ) > 3,
    1,
    0
)

PacketLoss ? = IF(SUMX(FILTER(SUMMMARIZE('Sheet1 (2)','Sheet1 (2)'[ParticipantName],"_PacketLossCalc",[PacketLossCalc]),[_PacketLossCalc]>0),[_PacketLossCalc])>0,1,0)

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

 

StefanoGrimaldi
Resident Rockstar
Resident Rockstar

ok, if they do match, you could try going over to power query, and do some tranformation to both column, make all letter lowercase, trim and dim the columns to delete all possible space remember even a blank space at the end of the cell its looked as a caracter in the strim. basiccaly ensure via power query there its no unwanted character or form that avoid from the relationship to find its coincidence. 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




StefanoGrimaldi
Resident Rockstar
Resident Rockstar

get both column over to a excel file, and math them to ensure you do have coincedence over in the relantionship, it have happend to me in cases the ids arent identical for any reason, if its that that there its no identical ID between columns you would need to explore those columns for a workaround. 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




@StefanoGrimaldi  Thanks.  I have already checked in the my data, its actually from excel only. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.