Black_magic100 Tracker

Re: Does Power BI have display folders to store and organize measures?

Black_magic100 — Thu, 09 May 2019 18:00:19 GMT

Now I have to go through all of my reports and organize everything......... =[ =[

Re: Does Power BI have display folders to store and organize measures?

Black_magic100 — Thu, 09 May 2019 17:54:44 GMT

OMG.... That is exactly what I meant lol. I currently have a table called _formulas, but this is what I was looking for. You have got to be kidding me

Refreshing API Call

Black_magic100 — Thu, 09 May 2019 17:52:39 GMT

Okay I did not realize this was a thing, but I am currently running into the issue that is brought up in this article: https://ideas.powerbi.com/forums/265200-power-bi/suggestions/9312540-make-functions-refreshable-when-the-data-source-is

Is there currently a workaround for getting my report to schedule refresh in the service? Is my only option to distribute the file manually

Re: Does Power BI have display folders to store and organize measures?

Black_magic100 — Thu, 09 May 2019 17:16:39 GMT

I already do that, but that is essentially just creating a blank table and adding formulas/calculations to it. I was moreso referring to literal folder icons within a table to organize field and measures. There are videos on youtube of exactly what I am referring to so I am not sure if they removed this or what happened.

Does Power BI have display folders to store and organize measures?

Black_magic100 — Thu, 09 May 2019 15:44:08 GMT

I have been using power bi for 5 months now and have always wondered why they haven't added this yet. However, I very old videos I am seeing display folders being used. Even when I google this, I can't find the answer. Am I really that big of an idiot and can somebody help me enable this?

Re: Having issues summing a table correctly

Black_magic100 — Mon, 06 May 2019 20:13:38 GMT

I switched some relationships around and ended up doing a simple SUM using a card visual. However, to answer your question, Cyclestar pay is a calculated column (SWITCH statement), but I also have it written as a measure (the measure is not currently being used).

Having issues summing a table correctly

Black_magic100 — Mon, 06 May 2019 18:22:59 GMT

I have a table visual in my report that pulls entirely from a single table. This table contains several calculated columns written in DAX. One of these columns takes the sum of the previous 3 and returns a value based on a SWITCH statement. I am attempting to SUM the values in this SWITCH statement column to get the total at the bottom. Previously I was using the following measure:

Cyclestar Pay Real Values =

IF(COUNTROWS(VALUES(ClassSchedule[ID of Class]))=1,

[CycleStar Pay],

SUMX(VALUES(ClassSchedule[ID of Class]), [CycleStar Pay]))

However, this broke when I changed from using all measures to calculated columns. I had to make this switch because calculated columns made more sense, but it broke the above measure and I can't seem to figure out how to sum the values in this table/column correctly.

Selecting first 15 days of month and remaining days with a slicer of 2 values

Black_magic100 — Thu, 02 May 2019 17:29:48 GMT

I want a user to be able to select a month and year in a slicer, but then in a completely separate slicer they are able to select 1 of 2 options.

Pay Period 1 = the first 15 days of the month/year they selected

Pay Period 2 = the remaining days of the month (obviously this varies)

Re: Need assistance with complex DAX measure

Black_magic100 — Thu, 02 May 2019 15:42:08 GMT

I figured it out!!!!!

Measure that did the trick for me:

Cyclestar Pay Real Values =

IF(COUNTROWS(VALUES(ClassSchedule[ClassScheduleID]))=1,

[CycleStar Pay],

SUMX(VALUES(ClassSchedule[ClassScheduleID]), [CycleStar Pay]))

Need assistance with complex DAX measure

Black_magic100 — Thu, 02 May 2019 15:29:00 GMT

Let me first explain the table below and each column...

Date - Simply a date from the related Date table

Paid Riders NOT Unlimited - Calculated by simply counting the rows of myTable1

Paid Riders ON Unlimited - Calculated by simply counting the rows of myTable2

New Riders Who Converted - Calculated by simply counting the rows of myTable3 AND also applying a basic filter where a column = "string"

New Riders who did NOT Convert - Ignore this column, not important

Total Paid Riders - Literally just adding up the first 3 columns

Pay - okay this is where I am running into issues... this column is actually a measure that is like so: CycleStar Pay = SWITCH ( TRUE(), [Total Paid Riders] <= 23, 40, [Total Paid Riders] <= 35, 70, [Total Paid Riders] <= 42, 100, [Total Paid Riders] >= 43, 125, 0). Now, I think you can begin to see my issue as the total for this "column" is simply returning $125 as the SWITCH statement clearly defines this.

Now, I think you can begin to see my issue as the total for this "column" is simply returning $125 as the SWITCH statement clearly defines this. I have been trying for the past 2 hours to figure out how to make this work. Somehow, I need to be able to look at each ClassScheduleID and determine the pay for each class, BUT also correctly display the total based off of the values of the SWITCH statement that are returned.

Can anybody help me out? Do I need to use variables somehow and then add them up? I have tried using formulas like ISfiltered or HASonevalue, but to no avail.

Help with very simple DAX code

Black_magic100 — Mon, 08 Apr 2019 16:42:47 GMT

Okay so I have a table that contains the following columns (these are not actual names):

1) mainID (a unique primary key ID)

2) Count1 (A simple DISTINCTCOUNT measure of an ID of a DIFFERENT table than above)

3) Count3 (A simple DISTINCTCOUNT measure of an ID of a DIFFERENT table than above)

4) FinalCount (This is simple a sum of the two measures above)

Now when I add these to a single table I get 5 rows because I am aggregating by the mainID.

However, when I add my final measure, which is seen below:

XXXXXX = SWITCH ( TRUE(), [yyyyyyyyy] <= 23, 40, [yyyyyyyyy] <= 35, 70, [yyyyyyyyy] <= 42, 100, [yyyyyyyyy] >= 43, 125, "Invalid")

I get a table that is now iterating through a ton of different values and the result is always showing 40 most likely because it is only counting 1 value? I feel like this should be very simple, but for whatever reason that last SWITCH() statement is not wanting to just look at the final column and add the appropriate 5 rows.

How to iterate through a column and make an API request for each row

Black_magic100 — Mon, 01 Apr 2019 19:27:13 GMT

I need to setup a column to act as a parameter. My api call looks something like https://xxxx.xxxx..com/v4/bitlinks/{param}/clicks where param is where I would need to iterate through each row.

Re: How to set RankX values if value = ***

Black_magic100 — Sat, 16 Mar 2019 19:01:28 GMT

Wow I tried to do the exact same thing, but failed miserably. I think the hardest part about DAX as I am slowly learning it is realizing how important the order of the syntax is. Definitely something I will learn over time, but I my mistakes always seem to derive from that. Stupid mistake for sure. Thank you friend.

How to set RankX values if value = ***

Black_magic100 — Fri, 15 Mar 2019 18:23:09 GMT

I have a table with 2 columns as seen below. In this table their are 20 ROWS, which declare a name of a location. Currently my formula ranks the values accordingly, but I need to adjust the ranking. How can I add to my formula a condition where if the Sum Column value = Null/Blank then RankX column = 20.

Formula:

CycleGiving and Private Rides MTD = CALCULATE(DISTINCTCOUNT(ClassSchedule[ClassScheduleID]),FILTER(ClassSchedule,RELATED('Calendar'[CurMonthOffset] ) = 0), FILTER(ClassSchedule,RELATED('Calendar'[FutureDate] ) = "Past"),FILTER('Classes@ClassCategory','Classes@ClassCategory'[CategoryId] IN {680, 681, 682}),FILTER(ClassSchedule,ClassSchedule[Cancelled] = BLANK()))

RankX formula:

CG/Private Rides Rank =

RANKX (

ALL(GeneralPrefs[LocationName]),

[CycleGiving and Private Rides MTD])

Sum Column | RankX Column

6,1

4,2

3,4

2,5

1,6

Blank,16

Re: Selecting the last value of last month

Black_magic100 — Wed, 13 Mar 2019 16:40:27 GMT

Okay so I was about to shoot myself trying to understand the DAX so naturally I turned to M/SQL to solve the issue. Ended up creating a new column that would fill with the last day of last month for every single row in my date table. I then did a simple conditional column where if the date = isLastDayofLastMonth then isLastDayofLastMonth ELSE notLastDayofLastMonth.

I then just created a simple measure that filtered only those values!

= CALCULATE(SUM('ClubDayStats'[Membershipdraftnextmonth_exclpastdue]),'Calendar'[isLastDayofPreviousMonth] = "isLastDayofPreviousMonth")

Re: Selecting the last value of last month

Black_magic100 — Wed, 13 Mar 2019 16:11:14 GMT

hmmm not exactly. I am sorry, but I left out the fact that there are 20 locations I am slicing by. So I need to have the locations on the left (1 row per location) and then have the PurchaseAmount next to it (Already calculated), then I need the last day of last month's value (what I am trying to figure out right now with your help, and then lastly a simple subtraction between those two measures to get the difference.

The purchase amount column changes daily and the other column would only update monthly.

Re: Selecting the last value of last month

Black_magic100 — Wed, 13 Mar 2019 15:31:40 GMT

There is nowhere on this site to upload a file, but here is the sample data for last month and this month. I just need February 28th

Logdate Membershipdraftnextmonth_exclpastdue
2019-02-01 2665.00
2019-02-02 4055.00
2019-02-03 5185.00
2019-02-04 8244.00
2019-02-05 16739.99
2019-02-06 18070.99
2019-02-07 19818.99
2019-02-08 21280.99
2019-02-09 23532.99
2019-02-10 24611.99
2019-02-11 25992.99
2019-02-12 27611.99
2019-02-13 29265.99
2019-02-14 30485.99
2019-02-15 32608.99
2019-02-16 34433.99
2019-02-17 36169.14
2019-02-18 37173.14
2019-02-19 40312.14
2019-02-20 41223.14
2019-02-21 43332.14
2019-02-22 45324.14
2019-02-23 45818.14
2019-02-24 47110.14
2019-02-25 48788.14
2019-02-26 49869.14
2019-02-27 55804.14
2019-02-28 55843.14
2019-03-01 4257.00
2019-03-02 5360.00
2019-03-03 6707.00
2019-03-04 10326.00
2019-03-05 18106.99
2019-03-06 18927.99
2019-03-07 20996.99
2019-03-08 23556.99
2019-03-09 25792.99
2019-03-10 27147.99
2019-03-11 28082.99
2019-03-12 30115.99

Re: Selecting the last value of last month

Black_magic100 — Wed, 13 Mar 2019 15:30:21 GMT

I have a calendar table.

Here is my newest measure, but it returns nothing,

End of Last Month = CALCULATE(SUM('ClubDayStats'[Membershipdraftnextmonth_exclpastdue] ), LASTDATE(PREVIOUSMONTH('ClubDayStats'[LogDate])))

When I try to use time intelligence functions it never works. I have spoken with so many people about it and there is nothing wrong with my date table. I even made sure to declare it as the date table.

Selecting the last value of last month

Black_magic100 — Wed, 13 Mar 2019 15:04:12 GMT

I have a simple table that aggregates data each day. I am trying to take a single value from this table that is from LAST month and I only want to SUM the LAST day of LAST month.

Using this formula:

Invoices Coming Due Next Month Excl Past Dues = CALCULATE(SUM(ClubDayStats[Membershipdraftnextmonth_exclpastdue]), ENDOFMONTH(ClubDayStats[LogDate]))

...I am able to get it to work by using date filters (month offsets) or by simply applying a date to the visual so that it has context.

However, I now need a measure that already has this context and only shows last month. So for this month, I would need the value of february 28th because it is the LAST day of LAST month.

How can you calculate day over day change?

Black_magic100 — Fri, 22 Feb 2019 18:52:47 GMT

I am using a typical calendar table and trying to take the count of a primary count of another related table. I am able to use the PREVIOUSDAY function to count the primary keys of yesterday, but how can I compare this versus the previous day of that day to show an overal day over day change for the selected month.