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
microsoftrookie
Helper II
Helper II

Specific Value across multiple tables

Hello, 

I am looking to get a specific number from a set of different tables that have been joined in relationships, for example I am looking to get for the month of January the most recent year for the number of contracts. Normally I would use a slicer and this would work however this number is supposed to be a default value if not enough slicers have been selected. Like these three columns in the following example, but these columns are split up across 3 different tabels. (as in 1 table for the month, 1 for the year, and one for the number of contracts) (these tables all have working relationships and are working correctly)Across multiple tabels values needed.JPG

My current measure is the following: IF(DISTINCTCOUNT(samplelabel[Month])=1 && DISTINCTCOUNT(sample[Year])=1, SUM(sample[Contracts]),CALCULATE(SUM(sample[Contracts]),FILTER(
CROSSJOIN(yeartable,samplelabel),AND(yeartable[Periodyearnumber]="2015",samplelabel[Month]="January")))). 

The first part of the measure works(up until the false) however I cannot seem to filter and pick a specific result across multiple tables. (as in I am looking for January(sample table 1) of 2015's(sample table 2's) contract number(sample table 3)) Is there a function I am forgeting?

Thank you all for your help,

Microsoftrookie.

P.S. I cannot set them all in one table due to Security and Confidentiality coding issues and company security policy.

3 REPLIES 3
v-ljerr-msft
Employee
Employee

Hi @microsoftrookie,

 

Could you post your table structures(including the relationships) with just some mock data, and the expected result? So that we can better assist on this issue. Smiley Happy

 

Regards

Hi there @v-ljerr-msft,

Thank you for replying so quickly. So here are some pictures samples of tables of what is going on. So in this table eveything is all in Text form with the exception of the Contracts numbers. Contracts table.JPG These are linked linked to another table that has the specific period and period types matched up. For example, dates and times tables.JPG And these connected by a relationship that is based on the index which is also in text form. I hope these would help you out a bit more and that im probably just forgetting a dax function. Thank you for your time.

Regards,
Microsoftrookie.

Hello again, @v-ljerr-msft

In my last post, I forgot to mention that I wanted to pull the Contracts number for a specific value for example: I would look for it to show the 1111 number specifically, when something other than one thing for both slicers were to be selected in the slicers. I'm sorry I forgot to include this in the last post. This would give me a default answer but I seem to missing a step when it comes to requesting a different table.

Thank again, 

Microsoft Rookie

 

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.