Writing DAX formulas or reformulate my table

[Anonymous]

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?

[AlB · Accepted solution]

@Anonymous 

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 

[AlB]

Hi @Anonymous 

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 

[Anonymous]

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]

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.