topic Re: Writing DAX formulas or reformulate my table in DAX Commands and Tips

Writing DAX formulas or reformulate my table

Anonymous — Mon, 23 Nov 2020 11:56:41 GMT

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?

Re: Writing DAX formulas or reformulate my table

AntrikshSharma — Mon, 23 Nov 2020 12:27:08 GMT

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.

Re: Writing DAX formulas or reformulate my table

AlB — Mon, 23 Nov 2020 12:28:28 GMT

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

Re: Writing DAX formulas or reformulate my table

Anonymous — Mon, 23 Nov 2020 12:54:28 GMT

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)

Re: Writing DAX formulas or reformulate my table

AlB — Mon, 23 Nov 2020 13:49:38 GMT

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