Create measure that behaves like don't summarize

[Anonymous]

Hi!

I need to create measures that behaves like "don't summarize". One among other reason is that I am using a measure table and hides other fact tables. Below you see a snapshot from a fact table. The two rightmost columns will be candidate for measures I want create. I want to point out that PeriodType is a kind of column which decides if the row is a month (2) or week (1) row. All columns will be available through slicers (dimensions). The behavior I want is the following. 

I want to create measures that behaves like "dont summarize". If I just add date and the measure it will only show the average value, and as we add more slicer data it needs to show unique values but not duplicates. Finally it will be like below

Hope somebody can give any advise to my little challenge

regards Geir

Date Key	Company Key	Project Key	Region Key	Location Key	Konsern Key	PeriodType	PerEGI	AccEEI
20120715	1	54	2	4	1	1	104,3087	99,99964
20120722	1	54	2	4	1	1	104,3087	99,99964
20120729	1	54	2	4	1	1	104,3087	99,99964
20120805	1	54	2	4	1	1	104,3087	99,99964
20120812	1	54	2	4	1	1	104,3087	99,99964
20120731	1	69	2	4	1	2	104,3087	99,99964
20120831	1	69	2	4	1	2	104,3087	99,99964
20120930	1	69	2	4	1	2	104,3087	99,99964
20121031	1	69	4	23	1	2	104,3087	99,99964
20121031	1	69	2	4	1	2	104,3087	99,99964

[Greg_Deckler]

I am not clear on this at all. What is the logic to get your two columns?

[Anonymous]

Hi again

I was probably a little too quick in trying to explain the problem. All of the "key" columns you see in the table are relattionship keys to the dimensions, while the last two are numeric key figures showing snapshots at one day. In the facts table there are two levels a monthly and a weekly based on period type key (1 is week, 2 is month). Thus, the key figures are not summable. In my data model, I hide the fact tables and create measures tables in stead. But it is a little unclear to me how to make a non-summable measure. There are certainly good techniques where one can probably use SUM but that never sums up anything other than the row one is on. So one way to summarize "don't summarize but as a measure.

That was the first thing I wondered about. The other thing I wonder about is a recommended way to make measure so that they adapt to what slicers you have added to the report. Often you first enter the key number and then add slicer data. I realize it is not feasible to do this one hundred percent, but it would have been fine to give some clues as to how the measure should be made, preferably by using variables etc. Nice if you can give some point of view, maybe you have written about this in your book 🙂

regards Geir

[dax]

Hi @Anonymous , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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