cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pbra
Frequent Visitor

Writing DAX formulas or reformulate my table

In the photo beneath is an example showed of my file in Excel. I would like to connect my date table in Power BI to the photo below. The first three columns are snapshots of three scanmoments. However, I would like to connect the three first columns to my date table. It isn't possible to connect al of the colums to this date table, so what is your advice? Develop a new table or write a new DAX script whereby there is a connection between the headers and 1 date, in stead of 3?

 

Example Excelsource.PNG

 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

@Pbra 

Yeah, you can create the relationships as explained before. Then place DateTable[Date] in the rows of a table visual and use these measures in the visual:

MeasureScannerAuto =
CALCULATE ( SUM ( Table1[Amount] ) )
MeasureScannerFabriek =
CALCULATE (
    SUM ( Table1[Amount] ),
    USERELATIONSHIP ( DateTable[Date], Table1[Scanner Fabriek] )
)
MeasureScannerNieuw =
CALCULATE (
    SUM ( Table1[Amount] ),
    USERELATIONSHIP ( DateTable[Date], Table1[Scanner Nieuw] )
)

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

@Pbra 

Yeah, you can create the relationships as explained before. Then place DateTable[Date] in the rows of a table visual and use these measures in the visual:

MeasureScannerAuto =
CALCULATE ( SUM ( Table1[Amount] ) )
MeasureScannerFabriek =
CALCULATE (
    SUM ( Table1[Amount] ),
    USERELATIONSHIP ( DateTable[Date], Table1[Scanner Fabriek] )
)
MeasureScannerNieuw =
CALCULATE (
    SUM ( Table1[Amount] ),
    USERELATIONSHIP ( DateTable[Date], Table1[Scanner Nieuw] )
)

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

AlB
Super User
Super User

Hi @Pbra 

You haven't explained what you ultimately want to do, what you'll use the relationships for. If you did, perhaps we could come up with a more accurate solution.

You can create one active relationship and two inactive ones, and then activate those as necessary with USERELATIONSHIP( )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

Pbra
Frequent Visitor

Thanks for your reply @AlB. In the end, I would like to use one date slicer. When I select this date, I would like to see the action of the three scanners on that selected day. 

 

For example;

I'll select date 2-11-2020, PBI gives me for;

 - Scanner auto: 34

 - Scanner fabriek: 34

 - Scanner nieuw: 12

 

(See photo for input)

 

AntrikshSharma
Resident Rockstar
Resident Rockstar

1. Either create relationship to all 3 and 1 of them stays active, and when you have to use other 2 relationships then use USERELATIONSHIP in CALCULATE, i.e.

 

CALCULATE ( [Total Amount], USERELATIONSHIP ( Table[Scanner Fabrierk], Dates[Date] ) )and 

CALCULATE ( [Total Amount], USERELATIONSHIP ( Table[Scanner nieuw], Dates[Date] ) )

 

2. or, don't create any realtionships at all and use TREATAS.

CALCULATE ( [Total Amount], TREATAS ( VALUES ( Dates[Date] ), Table[Scanner nieuw]  ) 

 

I would suggest that you go with the first option.

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors